﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Threading.Tasks;
using BCCommon;
using BCCommon.Enums;
using BCEntity.Sim.CompanyBelongArea;
using Dapper;

namespace BCData.Sim.CompanyBelongArea
{
    public class CompanyBelongAreaData : ICompanyBelongAreaData
    {
        private readonly IDatabaseContext databaseContext;
        private const string QUERY = @"SELECT A.CompanyBelongAreaId,A.CompanyId,A.CompanySubName,A.CompanyName,A.ProvinceId,A.CityId,A.ProvinceCode,A.CityCode,A,CountyId,A.CountyCode,A.CreateTime,A.UpdateTime,A.CreateAdminId,A.CreateAdminName,
                                            B.ProvinceName,C.CityName
                                        FROM CompanyBelongArea A
                                        INNER JOIN Province B ON A.ProvinceId=B.ProvinceId
                                        INNER JOIN City C ON A.CityId=C.CityId AND B.ProvinceId=C.ProvinceId";

        public CompanyBelongAreaData(IDatabaseContext databaseContext)
        {
            this.databaseContext = databaseContext;
        }

        public async Task<IEnumerable<CompanyBelongAreaEntity>> Query(int companyId)
        {
            string sql = string.Format("{0} WHERE A.CompanyId=?CompanyId", QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?CompanyId", companyId, DbType.Int32, size: 10);
            return await this.databaseContext.Slave.QueryAsync<CompanyBelongAreaEntity>(sql, parameters);
        }

        public async Task<int> SettingBelongArea(List<CompanyBelongAreaEntity> insertList, List<CompanyBelongAreaEntity> deleteList)
        {
            await GetDeleteSql(deleteList);
            await GetInsertSql(insertList);
            return 1;
        }

        private async Task<string[]> GetInsertSql(List<CompanyBelongAreaEntity> insertList)
        {
            List<string> sqls = new List<string>();
            foreach (var item in insertList)
            {
                string sql = SqlCommands.GetInsertSql("CompanyBelongArea", new string[] { "CompanyId", "CompanySubName", "CompanyName", "ProvinceId", "CityId", "ProvinceCode", "CityCode", "CountyId", "CountyCode",
                    "CreateAdminId","CreateAdminName" });
                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("?CompanyId", item.CompanyId, DbType.Int32, size: 10);
                parameters.Add("?CompanySubName", item.CompanySubName, DbType.AnsiString, size: 50);
                parameters.Add("?CompanyName", item.CompanyName, DbType.AnsiString, size: 50);
                parameters.Add("?ProvinceId", item.ProvinceId, DbType.Int64, size: 20);
                parameters.Add("?CityId", item.CityId, DbType.Int64, size: 20);
                parameters.Add("?CountyId", item.CountyId, DbType.Int64, size: 20);
                parameters.Add("?ProvinceCode", item.ProvinceCode, DbType.AnsiString, size: 20);
                parameters.Add("?CityCode", item.CityCode, DbType.AnsiString, size: 20);
                parameters.Add("?CountyCode", item.CountyCode, DbType.AnsiString, size: 20);
                parameters.Add("?CreateAdminId", item.CreateAdminId, DbType.Int64, size: 20);
                parameters.Add("?CreateAdminName", item.CreateAdminName, DbType.AnsiString, size: 20);
                await this.databaseContext.Master.ExecuteAsync(sql, parameters, databaseContext.Transaction);
            }
            return sqls.ToArray();
        }

        private async Task<string[]> GetDeleteSql(List<CompanyBelongAreaEntity> deleteList)
        {
            List<string> sqls = new List<string>();
            foreach (var item in deleteList)
            {
                string sql = @"DELETE A FROM AdminBelongArea A 
                            INNER JOIN DepartmentStaff B ON A.AdminId=B.AdminId 
                            INNER JOIN Department C ON B.DepartmentId=C.DepartmentId
                            INNER JOIN CompanyBelongArea D ON C.CompanyId=D.CompanyId AND D.ProvinceId=A.ProvinceId AND D.CityId=A.CityId
                        WHERE D.CompanyBelongAreaId=?CompanyBelongAreaId;";
                DynamicParameters parameters = new DynamicParameters();
                sql = string.Format("{0}{1}", sql, @"DELETE A FROM DepartmentBelongArea A 
                        INNER JOIN Department B ON A.DepartmentId=B.DepartmentId
                        INNER JOIN CompanyBelongArea C ON B.CompanyId=C.CompanyId AND C.ProvinceId=A.ProvinceId AND C.CityId=A.CityId
                        WHERE C.CompanyBelongAreaId=?CompanyBelongAreaId;");
                sql = string.Format("{0}{1}", sql, @"DELETE CompanyBelongArea FROM CompanyBelongArea 
                        WHERE CompanyBelongAreaId=?CompanyBelongAreaId;");
                parameters = new DynamicParameters();
                parameters.Add("?CompanyBelongAreaId", item.CompanyBelongAreaId, DbType.Int64, size: 20);
                await this.databaseContext.Master.ExecuteAsync(sql, parameters, databaseContext.Transaction);
            }
            return sqls.ToArray();
        }

        public async Task<CompanyBelongAreaEntity> GetBelongAreaCompany(string provinceId, string CityId, CompanyBusinessType? companyBusinessType)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?ProvinceId", provinceId, DbType.AnsiString, size: 20);
            parameters.Add("?CityId", CityId, DbType.AnsiString, size: 20);
            if (companyBusinessType.HasValue)
            {
                wheres.Add("D.BusinessType = ?BusinessType");
                parameters.Add("?BusinessType", companyBusinessType.Value.ToString(), DbType.AnsiString);
            }
            string sql = string.Format(@"{0} INNER JOIN Company D ON A.CompanyId=D.CompanyId
                                        WHERE A.ProvinceId=?ProvinceId AND A.CityId=?CityId {1} 
                                        ORDER BY A.CompanyId DESC LIMIT 1", QUERY,
                                        wheres.Count > 0 ? " AND " + string.Join(" AND ", wheres) : string.Empty);

            return await this.databaseContext.Slave.QueryFirstOrDefaultAsync<CompanyBelongAreaEntity>(sql, parameters);
        }

        public async Task<IEnumerable<CompanyBelongAreaEntity>> OtherBelongArea(int companyId)
        {
            string sql = string.Format(@"{0} INNER JOIN Company D ON A.CompanyId=D.CompanyId 
                    WHERE A.CompanyId<>?CompanyId AND IFNULL(D.IsHeadOffice,0)=0 AND IFNULL(D.IsBusinessHeadOffice,0)=0
                    GROUP BY A.ProvinceId,A.CityId", QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?CompanyId", companyId, DbType.Int32, size: 10);
            return await this.databaseContext.Slave.QueryAsync<CompanyBelongAreaEntity>(sql, parameters);
        }
    }
}
